﻿--Person-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]'))
DROP TABLE [Person]
GO
CREATE TABLE [Person](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ParentPersonKey] [uniqueidentifier] NULL,
	[RelationshipKey] [uniqueidentifier] NULL,
	[PersonName] [nvarchar](MAX) NOT NULL,
	[Gender] [int] NOT NULL DEFAULT 0,
	[Birthday] [datetime] NULL,
	[Constellation] [nvarchar](3) NULL,
	[Blood] [nvarchar](10) NULL,
	[RelationshipWeight] [int] NOT NULL,
	[Description] [nvarchar](200) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
--PersonRelationship-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]'))
DROP TABLE [PersonRelationship]
GO
CREATE TABLE [PersonRelationship](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ParentKey] [uniqueidentifier] NULL,
	[RelationName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_PersonRelationship] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
--Mobile-----------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'Mobile')
DROP TABLE [Mobile]
GO
CREATE TABLE [Mobile](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[PersonKey] [uniqueidentifier] NOT NULL,
	[MobileNumber] [varchar](15) NOT NULL,
	[CountryCode] [varchar](2) NOT NULL,
	[Enabed] [bit] NOT NULL DEFAULT 1,
	[Description] [nvarchar](100) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Mobile] PRIMARY KEY CLUSTERED ([MobileKey] ASC)) ON [PRIMARY]
GO
--Location-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]'))
DROP TABLE [Location]
GO
CREATE TABLE [Location](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[LocationKey] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](100) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
--Location-----------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'Location')
DROP TABLE [Location]
GO
CREATE TABLE [Location](
	[DwellingKey] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[PersonKey] [uniqueidentifier] NOT NULL,
	[Enabled] [bit] NOT NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Person_Dwelling] PRIMARY KEY CLUSTERED ([DwellingKey] ASC,[PersonKey] ASC)) ON [PRIMARY]
GO
--Telephone-----------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'Telephone')
DROP TABLE [Telephone]
GO
CREATE TABLE [Telephone](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[TelNumber] [varchar](15) NOT NULL,
	[LocationKey] [uniqueidentifier] NOT NULL,
	[Enabed] [bit] NOT NULL DEFAULT 1,
	[Description] [nvarchar](200) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Telephone] PRIMARY KEY CLUSTERED ([TelKey] ASC)) ON [PRIMARY]
GO
--Education-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Education]'))
DROP TABLE [Education]
GO
CREATE TABLE [Education](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[PersonKey] [uniqueidentifier] NOT NULL,
	[StartStamp] [datetime] NULL,
	[EndStamp] [datetime] NULL,
	[Description] [nvarchar](200) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Education] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
